Mega Code Archive
Char Functions
1) Accommodate the datatype difference by placing a TO_CHAR function around the LAST_STOCK_DATE
2) All dashes (-) were dropped from the translated string, and the digit 9 was translated into an asterisk
3) ASCII() returns the numeric value of the leftmost character of the string
4) ASCII(A) ASCII(Z) ASCII(A) ASCII(Z) ASCII(ABC)
5) ASCIISTR(x)
6) Ascii
7) Asciistr(4321)
8) Characters rather than spaces are trimmed
9) Check the data size for number type column
10) CHR() function returns a character corresponding to the number passed in as the argument
11) Combine INSTR and SUBSTR together
12) Combine length and translate
13) Combine nvl and to_char
14) Compose(de unistr(0300) )
15) COMPOSE(x)
16) CONCAT() concatenates two (or more) strings
17) CONTAINS(description, 10g near oracle) 0
18) CONTAINS(description, website) 3
19) CONVERT(x, source_char_set, dest_char_set)L Converts x from source_char_set to dest_char_set
20) Converting DATE to another language
21) Converting German date to DATE format
22) DECOMPOSE(x) Converts x to a Unicode string after decomposition in the same character set as x
23) Demo LEVEL inside of LPAD
24) Demo LEVEL inside of LPAD with
25) Demonstrates the LOWER function
26) Dump table column
27) Dump( ename, 16 )
28) Dump(90101,16)
29) Dump(reverse(90101),16)
30) From_template argument can contain more characters than to_template
31) Get the sub string position by using instr
32) If not found, then the replacing does not occur
33) If search pattern is not in the string, the INSTR function returns 0
34) If the character to be padded is longer than the requested total length, it will be trimmed to the exact total
35) If the INSTR pattern is not found, then the entire string would be returned
36) INITCAP a sentence
37) INITCAP() returns the character string passed into it with the first letter of each word capitalized
38) Initcap
39) INSTR
40) Instr and substr
41) Ise INSTR to format a column
42) LEADING
43) LENGTH
44) Length for a char type
45) Length with null value
46) LOWER
47) Lpad
48) LPAD() and RPAD()
49) LPAD(string, 10,)
50) LTRIM and RTRIM
51) LTRIM function removes leading characters
52) LTRIM(ABCBCA12345, ABC)
53) LTRIM(OPS$SPORANO, OPS$)
54) Ltrim
55) Nesting TRIM function
56) NVL
57) Nvl( e2 ename, NO BOSS! PARTY TIME! )
58) NVL() deals with date value
59) Nvl2 with null value
60) NVL2(x, value1, value2)
61) Padding based on value length
62) Padding department with and
63) Proximity searches with contains
64) Remove a letter from string with replace function
65) Replace
66) REPLACE() replaces every occurrence of the string specified as the search_string with the replacement_string
67) REPLACE(OPS$SPORANO, OPS$)
68) REPLACE(This is a test,t,XYZ)
69) Rpad
70) RPAD() functions behavior is identical to that of LPAD(), the only difference being the padding side
71) RTRIM function removes trailing characters
72) Rtrim
73) Rtrim char type column data
74) Select chr(71)chr(114)chr(111)chr(111)chr(118)chr(121) Cool!
75) Select initcap( table_name ) from user_tables
76) Select initcap(THESE WORDS will be INITcapped)
77) Select lower( table_name )
78) Select lpad( , 5, )
79) Select ltrim( Some String ) A String
80) Select nvl( , Yes is null ) Evaluate
81) Select replace( Oracle is great!, great, awesome )
82) Select rpad( , 5, )
83) Select substr( 1234567890, 5 )
84) Shifts the lowercase as well as uppercase characters in a column
85) Simple demo for CONCAT function
86) Simple demo for INITAP
87) Simple demo for INSTR function
88) Simple demo for LOWER() function
89) Simple demo for SUBSTR function
90) Simple demo for TRIM function
91) Simple demo for UPPER() function
92) Soundex
93) SOUNDEX() returns a character string containing the phonetic representation of a character
94) SOUNDEX(Lari)
95) SOUNDEX(whyte)
96) Split column value with
97) START_POSITION specifies which character in the supplied string to start with, and the optional length specifies how many c
98) StringString
99) Substr
100) SUBSTR and INSTR are used together
101) SUBSTR() returns a portion of string, beginning at numeric_position up to a specified substring_length characters long
102) Substr(date value,8)+16
103) Syntax
104) Test case-insensitivity with a simple mixed-case query
105) This will fail, because the datatypes of the two arguments are different
106) TRAILING
107) Translate
108) Translate vs replace
109) TRANSLATE()
110) TRANSLATE() returns a string where all occurrences of the from_template are replaced with corresponding characters in the t
111) TRANSLATE(555-143-3344, 1234567890,XXXXXXXXXX) phone
112) TRANSLATE(x, from_string, to_string)
113) TRIM
114) TRIM from both sides
115) TRIM Leading and Trailing Zeroes
116) TRIM(TRAILING A FROM ABCA) trail
117) Trim letter S from both sides
118) Trim space from a string
119) Trim trailing space from a string
120) UNISTR() accepts a string argument and returns its representation in a national character set
121) UNISTR(x)
122) Union all with dump reverse
123) Unistr(0300)
124) UPPER()
125) Use instr in an if statement in PL SQL
126) Use Length function in where statement
127) Use Nvl function in math calculation for null value
128) Use nvl in a function
129) Use NVL to check nullment in PLSQL
130) Use NVL() to convert number columns and date columns
131) Use nvl2 to deal with null salary
132) Use rpad to add extra space
133) Use rtim to remove space
134) Use substr and instr to extract column value
135) Use substr in IF statement
136) Use SUBSTR to decode a column
137) Use translate function in update statement
138) Use TRUNC function in NVL function
139) Use vsize for varchar2 value and nvarchar value
140) Using the TRANSLATE() Function
141) Vsize